package sql;

import java.sql.*;
import java.util.Properties;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.*;

import Logging.Monitor;

/**
 * This class encapsulates all the relevant operations needed to manipulate 
 * data in our MySql server instance   
 * @author Ron Galay
 */
public class CacheHandler {
	
	protected static String USER;
	protected static String PWD;
	protected static String URL;
	protected static String DB_NAME = "Cache";
	protected static String DRIVER;
	
	private static void init(Properties p) {
		
		// extracting the relevant data for connection to the MySql server
		USER = p.getProperty("DBServer.user");
		PWD= p.getProperty("DBServer.password");
		URL= p.getProperty("DBServer.url");
		DRIVER = p.getProperty("DBServer.driver");
	}
	
	public static void createCache(Properties p) throws ClassNotFoundException,
														SQLException {
		
		// reading the connection information
		init(p);

	    Connection con = null;
	    Statement stmt = null;
		try { 
			// load the Driver class. 
			Class.forName("com.mysql.jdbc.Driver"); 
			
			// create the connection using the static getConnection method 
			con = DriverManager.getConnection (URL, USER, PWD); 
			
			// create a Statement class to execute the SQL statement 
			stmt = con.createStatement();
			
			// creating the ProxyCache database
			
			// creating the cache table if not already exists
			String createTable = "CREATE TABLE IF NOT EXISTS Cache " +
									"(url VARCHAR(512), " +
									"headers TEXT, " +
									"content BLOB, " +
									"lastmodified VARCHAR(256)) " +
									"ENGINE=InnoDB;";

			stmt.execute(createTable);
			Monitor.write(null, "Created the table cache named Cache");
			
		} finally { // Close the connection. 
			if(stmt!=null){
			     try{stmt.close();}	catch(Exception e){e.printStackTrace();} }
			if(con!=null){
			     try{con.close();}	catch(Exception e){e.printStackTrace();} }
		} 
	}
	
	/**
	 * This method clears the content of a table named cache if exists
	 * @param p properties for the connection to the database
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public static void clear(Properties p) throws ClassNotFoundException, SQLException {

		// reading the connection information
		init(p);

		Connection con = null;
	    Statement stmt = null;
		try { 
			// load the Driver class. 
			Class.forName("com.mysql.jdbc.Driver"); 
			
			// create the connection using the static getConnection method 
			con = DriverManager.getConnection (URL, USER, PWD); 
			
			// create a Statement class to execute the SQL statement 
			stmt = con.createStatement();
			
			// creating the cache table if not already exists
			String createTable = "DELETE FROM Cache";

			stmt.execute(createTable);
			Monitor.write(null, "Removed the table cache named Cache");
			
		} finally { // Close the connection. 
			if(stmt!=null){
			     try{stmt.close();}	catch(Exception e){e.printStackTrace();} }
			if(con!=null){
			     try{con.close();}	catch(Exception e){e.printStackTrace();} }
		} 
	}
	
	/**
	 * This method prints the entire content of the cache to the log
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 * @throws IOException 
	 */
	public static void printCacheContent(Properties p) throws 
														ClassNotFoundException,
														SQLException, 
														IOException {
		
		// reading the connection information
		init(p);

	    Connection con = null;
	    Statement stmt = null;
	    ResultSet rs = null;
		try { 
			// load the Driver class
			Class.forName("com.mysql.jdbc.Driver"); 
			
			// create the connection using the static getConnection method 
			con = DriverManager.getConnection (URL, USER, PWD); 
			
			// create a Statement class to execute the SQL statement 
			stmt = con.createStatement();
			
			// get all the content of the cache
			String  getContent = "SELECT * FROM Cache";
			rs = stmt.executeQuery(getContent);
			
			Monitor.write(null, "Printing the content of the cache");
			// Iterate through the ResultSet, displaying two values 
			while (rs.next()) { 
				String outString = 	"\turl= " + rs.getString("url") + "\n" + 
									"\t\theaders= " + rs.getString("headers") + "\n" +
									"\t\tlastmodified= " + rs.getString("lastmodified") + "\n";
				System.out.println();
				Blob blob= rs.getBlob("content");
				InputStream readContent = new ByteArrayInputStream(new byte[0]);
				if(blob != null)
					readContent = blob.getBinaryStream();
				outString += "\t\tcontent= ";
				int c;
			      while ((c = readContent.read())!=-1)
			    	  outString += (char)c;
						
				Monitor.write(null, outString);
			}
			
		} finally { // Close the connection. 
			if(rs!=null){
			     try{rs.close();}	catch(Exception e){e.printStackTrace();} }
			if(stmt!=null){
			     try{stmt.close();}	catch(Exception e){e.printStackTrace();} }
			if(con!=null){
			     try{con.close();}	catch(Exception e){e.printStackTrace();} }
		} 
	}

	/**
	 * This method inserts the content of the packet to the database.
	 * If the record already exists remove it and insert the new one instead.
	 * In case of an error or an exception no recovery measures are tried.
	 * @param url  
	 * @param headers should be passed encoded by US-ASCII also includes the statusline
	 * @param content
	 * @param lastmodified  
	 * @param p  
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public static void insertRecord(URL url, String headers, 
									ByteArrayInputStream content, 
									String lastmodified, Properties p) 
									throws ClassNotFoundException, SQLException {
		
		// reading the connection information
		init(p);

		// remove record by url
		removeRecord(url,p);
		
	    Connection con = null;
		try { 
			// load the Driver class
			Class.forName("com.mysql.jdbc.Driver"); 
			
			// create the connection using the static getConnection method 
			con = DriverManager.getConnection (URL, USER, PWD); 
			
			
			// inserting a record to the database
			PreparedStatement insertRecord = con.prepareStatement("INSERT INTO Cache " +
											 "(url,headers,content,lastmodified) VALUES" +
											 "(?,?,?,?)");
			
			// setting the parameters instead of the question marks
			insertRecord.setURL(1, url); 
			insertRecord.setString(2, headers); 
			insertRecord.setBlob(3, content); 
			insertRecord.setString(4, lastmodified);
			insertRecord.executeUpdate();
			Monitor.write(null, "Inserted the record:"+insertRecord.toString());
			
		} finally { // Close the connection. 
			if(con!=null){
			     try{con.close();}	catch(Exception e){e.printStackTrace();} }
		} 
	}
	
	/**
	 * This method tries to remove the record with the passed url if exists	
	 * @param url
	 * @param p
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public static void removeRecord(URL url, Properties p) 
														throws 
														ClassNotFoundException, 
														SQLException {
		// reading the connection information
		init(p);
		
		Connection con = null;
		try {
			// load the Driver class
			Class.forName("com.mysql.jdbc.Driver");
			
			// create the connection using the static getConnection method
			con = DriverManager.getConnection(URL, USER, PWD);
			
			// get content of the cache according to the input URL
			PreparedStatement removeRecord = con.prepareStatement("DELETE FROM Cache WHERE url = ?");
			
			// setting the parameters instead of the question marks
			removeRecord. setURL(1, url);
			
			//executing the query
			Monitor.write(null,	"Deleted the record:" + removeRecord.toString());
			removeRecord.executeUpdate();
			
		} finally { // Close the connection.
			if(con!=null){
			     try{con.close();}	catch(Exception e){e.printStackTrace();} }
		}
	}

	public static ResponseWrapper getRecord(URL url, Properties p) 
														throws
														SQLException,
														ClassNotFoundException {
		// reading the connection information
		init(p);
		
		Connection con = null;
		ResultSet rs = null;
		ResponseWrapper response = new ResponseWrapper();
		try {
			// load the Driver class
			Class.forName("com.mysql.jdbc.Driver");
			
			// create the connection using the static getConnection method
			con = DriverManager.getConnection(URL, USER, PWD);
			
			// get content of the cache according to the input URL
			PreparedStatement selectRecord = con.prepareStatement("SELECT * FROM Cache WHERE url = ?");
			
			// setting the parameters instead of the question marks
			selectRecord. setURL(1, url);
			
			//executing the query
			Monitor.write(null,	"Selected the record:" + selectRecord.toString());
			rs = selectRecord.executeQuery();
			if(!rs.last()) return null;
			
			//retrieving results
			rs.first();
			response.setUrl(rs.getURL(1));
			response.setHeaders(rs.getString(2));
			response.setContent((rs.getBlob(3) != null)?rs.getBlob(3).getBinaryStream():null);
			response.setLastmodified(rs.getString(4));
			
		} finally { // Close the connection.
			if(con!=null){
			     try{con.close();}	catch(Exception e){e.printStackTrace();} }
		}
		return response;
	}
}
